﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using IHISCETeim.Library;
namespace IHISCETeim.Web.Export.FTradeModel
{
    public partial class FTradeExportReport7 : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "应收帐款_风险部奖金扣罚表.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "奖金扣罚表";
            }

        }
        protected DateTime lastDaoQiRi
        {
            get
            {
                DateTime dt = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);

                return dt.AddMonths(1).AddDays(-1);

            }
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            //营业经理权限 2014-02-21
            string agentList = base.FTradeAgentPowerSqlWhere(UserId);
            string wheresql = string.IsNullOrEmpty(agentList) ? " 1=1 " : string.Format(" JingXiaoShang in ({0})", agentList);

            //初始化
            InitializeWorkbook();
            ISheet sheet1 = hssfworkbook.GetSheet(SheetName);
            sheet1.GetRow(0).GetCell(0).SetCellValue(string.Format("营业经理{0}月份应收帐款逾期扣减：",lastDaoQiRi.ToString("MM")));
            DataTable exportDT = FTradeReportController.Report7GetReportDate(wheresql, lastDaoQiRi.ToString("yyyy-MM-dd"))[0];
            int rowindexBase = 2;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            foreach (DataRow dr in exportDT.Rows)
            {
                HSSFRow row1 = (HSSFRow)sheet1.CreateRow(rowReal);
                // row1.CreateCell(0).SetCellValue(1);
                row1.CreateCell(0).SetCellValue(dr["YingYeJingLi"].ToString());//
                row1.CreateCell(1).SetCellValue(dr["JingXiaoShang"].ToString());//
                row1.CreateCell(2).SetCellValue(dr["HeTongHao"].ToString());
                row1.CreateCell(3).SetCellValue(dr["JiHao"].ToString());
                row1.CreateCell(4).SetCellValue(GetDBDouble(dr["WeiShouKuanXiang"]));
                row1.CreateCell(5).SetCellValue(GetDBString(dr["DaoQiRi"]));
                row1.CreateCell(6).SetCellValue(GetDBDouble(dr["yiqijine"]));
                row1.CreateCell(7).SetCellValue(dr["accrualDay"].ToString());
               // row1.CreateCell(8).SetCellValue(GetDBDouble(dr["逾期30天内"]));
               // row1.CreateCell(9).SetCellValue(GetDBDouble(dr["逾期90天"]));
                row1.CreateCell(10).SetCellValue(GetDBString(dr["ZDXiaoShouShiJian"]));
                row1.CreateCell(11).SetCellValue(GetDBString(dr["ZDXiaoShouState"]));
                row1.CreateCell(12).SetCellValue(GetDBString(dr["XiaoShouLeiXing"]));

                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

            HSSFRow rowsum = (HSSFRow)sheet1.CreateRow(rowReal);
            // row1.CreateCell(0).SetCellValue(1);
            rowsum.CreateCell(1).SetCellValue("合计：");//经销商
         //   rowsum.CreateCell(1).SetCellFormula(string.Format("sum(B3:B{0})", rowReal));//未支付金额
          //  rowsum.CreateCell(2).SetCellFormula(string.Format("sum(C3:C{0})", rowReal));//未到期金额
          //  rowsum.CreateCell(3).SetCellFormula(string.Format("sum(D3:D{0})", rowReal));
            rowsum.CreateCell(4).SetCellFormula(string.Format("sum(E3:E{0})", rowReal));
            //rowsum.CreateCell(5).SetCellFormula(string.Format("sum(F3:F{0})", rowReal));
            rowsum.CreateCell(6).SetCellFormula(string.Format("sum(G3:G{0})", rowReal));
            //rowsum.CreateCell(7).SetCellFormula(string.Format("sum(H3:H{0})", rowReal));
           // rowsum.CreateCell(8).SetCellFormula(string.Format("sum(I3:I{0})", rowReal));
           // rowsum.CreateCell(9).SetCellFormula(string.Format("sum(J3:J{0})", rowReal));
           // rowsum.CreateCell(10).SetCellFormula(string.Format("sum(K3:K{0})", rowReal));
           // rowsum.CreateCell(11).SetCellFormula(string.Format("sum(L3:L{0})", rowReal));

            //Force excel to recalculate all the formula while open
            sheet1.ForceFormulaRecalculation = true;

            //写入
            WriteToFile();
        }
        private Double GetDBDouble(object v)
        {
            if (v is DBNull)
                return 0;
            return Convert.ToDouble(v);
        }
        private string GetDBString(object v)
        {
            if (v is DBNull)
                return "";
            return v.ToString();
        }

        protected override string GetXlsFileName(string oldFileName)
        {
            //return base.GetXlsFileName(oldFileName);

            return string.Format("{0}{1}.xls", "奖金扣罚表", DateTime.Now.ToString("yyyy-MM-dd")).Trim();
        }
    }
}